package com.csw.mysqldate.controller;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.util.CellRangeAddressList;

public class ExcelLinkage {
	// 样式
	private HSSFCellStyle cellStyle;
	// 初始化省份数据
	private List<String> province = new ArrayList<String>(Arrays.asList("湖南", "广东"));
	// 初始化数据（湖南的市区）
	private List<String> hnCity = new ArrayList<String>(Arrays.asList("长沙市", "邵阳市"));
	// 初始化数据（广东市区）
	private List<String> gdCity = new ArrayList<String>(Arrays.asList("深圳市", "广州市"));

	public static void main(String[] args) {
		ExcelLinkage linkage = new ExcelLinkage();
		linkage.export();
	}

	public void setDataCellStyles(HSSFWorkbook workbook) {
		cellStyle = workbook.createCellStyle();
		// 设置边框
		/*
		 * cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		 * cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		 * cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		 * cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 设置背景色
		 * cellStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
		 * cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 设置居中
		 * cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		 */
		// 设置字体
		HSSFFont font = workbook.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) 11); // 设置字体大小
		cellStyle.setFont(font);// 选择需要用到的字体格式
		// 设置单元格格式为文本格式（这里还可以设置成其他格式,可以自行百度）
		HSSFDataFormat format = workbook.createDataFormat();
		cellStyle.setDataFormat(format.getFormat("@"));
	}

	/**
	 * 创建数据域（下拉联动的数据）
	 *
	 * @param workbook
	 * @param hideSheetName 数据域名称
	 */
	private void creatHideSheet(HSSFWorkbook workbook, String hideSheetName) {
		// 创建数据域
		HSSFSheet sheet = workbook.createSheet(hideSheetName);
		// 用于记录行
		int rowRecord = 0;
		// 获取行（从0下标开始）
		HSSFRow provinceRow = sheet.createRow(rowRecord);
		// 创建省份数据
		this.creatRow(provinceRow, province);
		// 根据省份插入对应的市信息
		rowRecord++;
		for (int i = 0; i < province.size(); i++) {
			List<String> list = new ArrayList<String>();
			// 我这里是写死的 ， 实际中应该从数据库直接获取更好
			if ("湖南".equals(province.get(i).toString())) {
				// 将省份名称放在插入市的第一列， 这个在后面的名称管理中需要用到
				list.add(0, province.get(i).toString());
				list.addAll(hnCity);
			} else {
				list.add(0, province.get(i).toString());
				list.addAll(gdCity);
			}
			// 获取行
			HSSFRow hssfRow = sheet.createRow(rowRecord);
			// 创建省份数据
			this.creatRow(hssfRow, list);
			rowRecord++;
		}
	}

	/**
	 * 创建一列数据
	 *
	 * @param currentRow
	 * @param
	 */
	public void creatRow(HSSFRow currentRow, List<String> text) {
		if (text != null) {
			int i = 0;
			for (String cellValue : text) {
				// 注意列是从（1）下标开始
				HSSFCell userNameLableCell = currentRow.createCell(i++);
				userNameLableCell.setCellValue(cellValue);
			}
		}
	}

	/**
	 * 名称管理
	 *
	 * @param workbook
	 * @param hideSheetName 数据域的sheet名
	 */
	private void creatExcelNameList(HSSFWorkbook workbook, String hideSheetName) {
		Name name;
		name = workbook.createName();
		// 设置省名称
		name.setNameName("province");
		name.setRefersToFormula(hideSheetName + "!$A$1:$" + this.getcellColumnFlag(province.size()) + "$1");
		// 设置省下面的市
		for (int i = 0; i < province.size(); i++) {
			List<String> num = new ArrayList<String>();
			if ("湖南".equals(province.get(i))) {
				name = workbook.createName();
				num.add(0, province.get(i).toString());
				num.addAll(hnCity);
				name.setNameName(province.get(i).toString());
				name.setRefersToFormula(
						hideSheetName + "!$B$" + (i + 2) + ":$" + this.getcellColumnFlag(num.size()) + "$" + (i + 2));
			} else {
				name = workbook.createName();
				num.add(0, province.get(i).toString());
				num.addAll(gdCity);
				name.setNameName(province.get(i).toString());
				name.setRefersToFormula(
						hideSheetName + "!$B$" + (i + 2) + ":$" + this.getcellColumnFlag(num.size()) + "$" + (i + 2));
			}
		}
	}

	// 根据数据值确定单元格位置（比如：28-AB）
	private String getcellColumnFlag(int num) {
		String columFiled = "";
		int chuNum = 0;
		int yuNum = 0;
		if (num >= 1 && num <= 26) {
			columFiled = this.doHandle(num);
		} else {
			chuNum = num / 26;
			yuNum = num % 26;
			columFiled += this.doHandle(chuNum);
			columFiled += this.doHandle(yuNum);
		}
		return columFiled;
	}

	private String doHandle(final int num) {
		String[] charArr = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R",
				"S", "T", "U", "V", "W", "X", "Y", "Z" };
		return charArr[num - 1].toString();
	}

	/**
	 * 使用已定义的数据源方式设置一个数据验证
	 *
	 * @param formulaString
	 * @param naturalRowIndex
	 * @param naturalColumnIndex
	 * @return
	 */
	public DataValidation getDataValidationByFormula(String formulaString, int naturalRowIndex,
			int naturalColumnIndex) {
		// 加载下拉列表内容
		DVConstraint constraint = DVConstraint.createFormulaListConstraint(formulaString);
		// 设置数据有效性加载在哪个单元格上。
		// 四个参数分别是：起始行、终止行、起始列、终止列
		int firstRow = naturalRowIndex;
		int lastRow = naturalRowIndex;
		int firstCol = naturalColumnIndex - 1;
		int lastCol = naturalColumnIndex - 1;
		CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
		// 数据有效性对象
		DataValidation dataValidation = new HSSFDataValidation(regions, constraint);
		return dataValidation;
	}

	/**
	 * 创建一列数据
	 *
	 * @param hssfSheet
	 */
	public void creatAppRow(HSSFSheet hssfSheet, int naturalRowIndex) {
		// 获取行
		HSSFRow hssfRow = hssfSheet.createRow(naturalRowIndex);
		HSSFCell cell = hssfRow.createCell(0);
		cell.setCellValue("");
		cell.setCellStyle(cellStyle);
		HSSFCell cell1 = hssfRow.createCell(1);
		cell1.setCellValue("");
		cell1.setCellStyle(cellStyle);
		// 得到验证对象
		DataValidation dataValidationList1 = this.getDataValidationByFormula("province", naturalRowIndex, 1);
		DataValidation dataValidationList2 = this
				.getDataValidationByFormula("INDIRECT($A" + (naturalRowIndex + 1) + ")", naturalRowIndex, 2);
		// 工作表添加验证数据
		hssfSheet.addValidationData(dataValidationList1);
		hssfSheet.addValidationData(dataValidationList2);
	}

	public void export() {
		try {
			File file = new File("F:/excel.xls");
			FileOutputStream outputStream = new FileOutputStream(file);
			// 创建excel
			HSSFWorkbook workbook = new HSSFWorkbook();
			// 设置sheet 名称
			HSSFSheet excelSheet = workbook.createSheet("excel");
			// 设置样式
			this.setDataCellStyles(workbook);
			// 创建一个隐藏页和隐藏数据集
			this.creatHideSheet(workbook, "shutDataSource");
			// 设置名称数据集
			this.creatExcelNameList(workbook, "shutDataSource");
			// 创建一行数据
			for (int i = 0; i < 50; i++) {
				this.creatAppRow(excelSheet, i);
			}
			workbook.write(outputStream);
			outputStream.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}
